package com.qianlima.jpademo.repository;

import com.qianlima.jpademo.dto.UserDTO;
import com.qianlima.jpademo.dto.UserMapDTO;
import com.qianlima.jpademo.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @description:
 * @author：allms
 * @date: 2023/4/7 15:59
 */
public interface UserRepository extends JpaRepository<User, Long> {
    // 使用JPA的方法命名规则来查询User实体对象，并级联查询Role实体对象
    List<User> findByRoleName(String roleName);

    // 使用@Query注解来指定JPQL语句，查询User实体对象，并级联查询Role实体对象
    @Query(value = "select u from User u where u.role.name = ?1")
    List<User> findUserByRoleName(String roleName);

    @Query(value = "select u.id as id, u.name as name, u.age as age, r.name as roleName from sys_user u join sys_role r on u.role_id = r.id", nativeQuery = true)
    List<Object[]> findUserAndRole();

    @Query(value = "select u.id as id, u.name as name, u.age as age, r.name as roleName from sys_user u join sys_role r on u.role_id = r.id", nativeQuery = true)
    List<UserMapDTO> findLocalUser();

    @Query(value = "select new map(u.id as id, u.name as name, r.name as roleName) from User u join Role r on u.role.id = r.id")
    List<Map<String, Object>> findUserAndRole2();


    @Query(value = "select new com.qianlima.jpademo.dto.UserDTO(u.id,u.name,u.age,r.name) from User u join Role r on u.role.id = r.id")
    List<UserDTO> findUserRoleDTO();

}
